use std::{convert, string};

use crate::{ai_agent_plat, coze_constants, utils::common_utils};
use common::{
    error::AppError,
    page::TableDataInfo,
    utils::date_util::date_is_query_format, // 假设使用之前的日期验证函数
};
use serde_json::{json, Map, Value};
use sqlx::{query_builder::QueryBuilder, MySql, MySqlPool};

use super::model::{AddAiAgentVo, AiAgent, AiAgentDo, ListAiAgentQuery, UpdateAiAgentVo};
use tracing::error;

use super::super::utils::http_utils;
pub async fn select_ai_agent_list(db: &MySqlPool, params: ListAiAgentQuery) -> Result<TableDataInfo<AiAgent>, AppError> {
    // 初始化查询构建器，使用参数化方式构建SQL
    let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new("SELECT * FROM ai_agent WHERE 1=1 AND data_sta != 'D'");
    let mut count_builder: QueryBuilder<MySql> = QueryBuilder::new("SELECT count(*) FROM ai_agent WHERE 1=1 AND data_sta != 'D'");

    // 处理名称查询条件 - 使用参数化LIKE查询
    // if let Some(ai_agent_plat_id) = params.ai_agent_plat_id {
    //     let trimmed = ai_agent_plat_id;
    //     if !trimmed() {
    //         // 仅在非空时处理，避免空字符串导致的意外查询
    //         let like_pattern = format!("%{}%", trimmed);
    //         // 对两个构建器使用相同的参数化处理
    //         query_builder.push(" AND ai_agent_plat_name LIKE ?").push_bind(like_pattern.clone());
    //         count_builder.push(" AND ai_agent_plat_name LIKE ?").push_bind(like_pattern);
    //     }
    // }

    // 处理账号名称查询条件 - 修复原代码中的字段名错误
    if let Some(ai_agent_bot_id) = params.ai_agent_bot_id {
        let trimmed = ai_agent_bot_id.trim();
        if !trimmed.is_empty() {
            let like_pattern = format!("%{}%", trimmed);
            // 原代码中这里错误地使用了ai_agent_plat_name，已修正为正确的字段名
            query_builder
                .push(" AND ai_agent_bot_id LIKE ?")
                .push_bind(like_pattern.clone());
            count_builder
                .push(" AND ai_agent_bot_id LIKE ?")
                .push_bind(like_pattern);
        }
    }

    // 处理开始时间条件 - 增加日期验证错误返回
    if let Some(begin_time) = params.begin_time {
        let trimmed = begin_time.trim();
        if !trimmed.is_empty() {
            // 日期验证失败时返回错误，而不是仅打印
            match date_is_query_format(trimmed) {
                Ok(()) => (),
                Err(e) => {
                    let errmsg = AppError::ValidationFailed(format!("基本错误,{}", e.to_string()));
                    return Err(errmsg);
                }
            }
            // date_is_query_format(trimmed)?;
            query_builder
                .push(" AND create_time >= ?")
                .push_bind(trimmed.to_string());
            count_builder
                .push(" AND create_time >= ?")
                .push_bind(trimmed.to_string());
        }
    }

    // 处理结束时间条件
    if let Some(end_time) = params.end_time {
        let trimmed = end_time.trim();
        if !trimmed.is_empty() {
            match date_is_query_format(trimmed) {
                Ok(()) => (),
                Err(e) => {
                    let errmsg = AppError::ValidationFailed(format!("基本错误,{}", e.to_string()));
                    return Err(errmsg);
                }
            }

            query_builder
                .push(" AND create_time <= ?")
                .push_bind(trimmed.to_string());
            count_builder
                .push(" AND create_time <= ?")
                .push_bind(trimmed.to_string());
        }
    }

    // 执行总数查询
    let total: (i64,) = count_builder
        .build_query_as()
        .fetch_one(db)
        .await
        .map_err(|e| {
            error!("[DB_ERROR] 统计AI代理平台总数失败: {}", e);
            AppError::from(e)
        })?;

    // 处理分页参数 - 确保参数合法
    let page_num = params.page_num.unwrap_or(1).max(1); // 页码至少为1
    let page_size = params.page_size.unwrap_or(10).clamp(1, 100); // 限制每页条数在1-100之间
    let offset = (page_num - 1) * page_size;

    // 添加排序和分页 - 使用参数化绑定分页参数
    query_builder
        .push(" ORDER BY ai_agent_id ASC, created_time DESC LIMIT ")
        .push_bind(page_size)
        .push(" OFFSET ")
        .push_bind(offset);

    println!("page_size:{}", page_size);
    println!("offset:{}", offset);
    println!("sql:{}", query_builder.sql());
    // 执行列表查询
    let rows = query_builder
        .build_query_as()
        .fetch_all(db)
        .await
        .map_err(|e| {
            error!("[DB_ERROR] 查询AI代理平台列表失败: {}", e);
            AppError::from(e)
        })?;

    Ok(TableDataInfo::new(rows, total.0))
}

pub async fn select_ai_agent_by_id(db: &MySqlPool, ai_agent_id: u64) -> Result<AiAgent, AppError> {
    let ai_agent_plat = sqlx::query_as!(
        AiAgent,
        "SELECT * FROM ai_agent WHERE ai_agent_id = ?",
        ai_agent_id
    )
    .fetch_one(db)
    .await?;
    Ok(ai_agent_plat)
}

pub async fn select_do_ai_agent_by_id(db: &MySqlPool, ai_agent_id: u64) -> Result<AiAgentDo, AppError> {
    println!("ai_agent_id:ai_agent_id {:?}", ai_agent_id);
    let ai_agent = sqlx::query_as!(
        AiAgent,
        "SELECT * FROM ai_agent WHERE ai_agent_id = ?",
        ai_agent_id
    )
    .fetch_one(db)
    .await?;

    println!("结构体信息2:ai_agent {:?}", ai_agent);
    let ai_agent_plat = ai_agent_plat::service::select_ai_agent_plat_by_id(db, ai_agent.ai_agent_plat_id).await?;

    println!("结构体信息:ai_agent_plat {:?}", ai_agent_plat);
    let mut ai_agent_do: AiAgentDo = common_utils::convert(ai_agent);
    ai_agent_do.ai_agent_plat_access_token = ai_agent_plat.ai_agent_plat_access_token;
    Ok(ai_agent_do)
}

pub async fn delete_ai_agent_by_id(db: &MySqlPool, ai_agent_id: u64) -> Result<u64, AppError> {
    // RuoYi 的删除是逻辑删除，更新 del_flag 字段
    let result = sqlx::query!(
        "UPDATE ai_agent SET data_sta = 'D' WHERE ai_agent_id = ?",
        ai_agent_id
    )
    .execute(db)
    .await?;

    Ok(result.rows_affected())
}

pub async fn add_ai_agent(db: &MySqlPool, ai_agent: AddAiAgentVo, user_name: &str) -> Result<u64, AppError> {
    let result = sqlx::query!(
        r#"
            INSERT INTO ai_agent (
            ai_agent_plat_id, 
            ai_agent_bot_id, 
            ai_agent_name, 
            ai_agent_icon_url, 
            ai_agent_description, 
            ai_agent_other, 
            created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        "#,
        &ai_agent.ai_agent_plat_id,
        &ai_agent.ai_agent_bot_id,
        &ai_agent.ai_agent_name,
        &ai_agent.ai_agent_icon_url,
        &ai_agent.ai_agent_description,
        &ai_agent.ai_agent_other,
        user_name
    )
    .execute(db)
    .await?;

    Ok(result.rows_affected())
}

pub async fn update_ai_agent(db: &MySqlPool, ai_agent_vo: UpdateAiAgentVo, user_name: &str) -> Result<u64, AppError> {
    let result = sqlx::query!(
        r#"
            UPDATE ai_agent
            SET ai_agent_plat_id = ?, 
            ai_agent_bot_id = ?, 
            ai_agent_name = ?, 
            ai_agent_icon_url = ?, 
            ai_agent_description = ?, 
            ai_agent_other = ?, 
            updated_by = ?, 
            data_sta = 'U'
            WHERE ai_agent_id = ?
        "#,
        ai_agent_vo.ai_agent_plat_id,
        ai_agent_vo.ai_agent_bot_id,
        ai_agent_vo.ai_agent_name,
        ai_agent_vo.ai_agent_icon_url,
        ai_agent_vo.ai_agent_description,
        ai_agent_vo.ai_agent_other,
        user_name,
        ai_agent_vo.ai_agent_id
    )
    .execute(db)
    .await?;
    Ok(result.rows_affected())
}

pub async fn get_ai_agent_bot_list(db: &MySqlPool, ai_agent_plat_id: u64) -> Result<Value, AppError> {
    if ai_agent_plat_id == 0 {
        let errmsg = AppError::ValidationFailed("ai_agent_plat_id 为空".to_string());
        return Err(errmsg);
    }

    let ai_agent_plat = ai_agent_plat::service::select_ai_agent_plat_by_id(db, ai_agent_plat_id).await?;
    let ai_agent_plat_access_token = ai_agent_plat
        .ai_agent_plat_access_token
        .clone() // 克隆 String（若原字段是 &Option<String>，则用 .as_ref().cloned()）
        .ok_or_else(|| AppError::ValidationFailed("token 为空".to_string()));

    let ai_agent_plat_workspace_id = ai_agent_plat
        .ai_agent_plat_workspace_id
        .clone() // 克隆 String（若原字段是 &Option<String>，则用 .as_ref().cloned()）
        .ok_or_else(|| AppError::ValidationFailed("ai_agent_plat_workspace_id 为空".to_string()));

    let ai_agent_bot_list = coze_constants::bots(
        &ai_agent_plat_access_token.unwrap(),
        &ai_agent_plat_workspace_id.unwrap(),
    )
    .await;

    match ai_agent_bot_list {
        Ok(data) => {
            // 成功：处理返回的 JSON 数据（data1 是 Value 类型）
            let pretty_json = serde_json::to_string_pretty(&data)?;
            println!("请求成功: {:?}", pretty_json);

            // 1. 安全提取 workspaces 数组（避免 unwrap() 直接 panic）
            let items = data
                .get("data") // 获取顶层 "data" 字段（Option<&Value>）
                .ok_or_else(|| AppError::ValidationFailed("未找到 data 字段".to_string()))? // 字段缺失报错
                .get("items") // 获取 "items" 字段
                .ok_or_else(|| AppError::ValidationFailed("未找到 items 字段".to_string()))?
                .as_array() // 转为数组（Option<&Vec<Value>>）
                .ok_or_else(|| AppError::ValidationFailed("items 不是数组类型".to_string()))?;
            // 2. 循环遍历数组，提取每个元素的 id
            let mut new_array = Vec::new();
            for item in items {
                // 确保当前元素是 JSON 对象
                let obj = item
                    .as_object()
                    .ok_or_else(|| AppError::ValidationFailed("元素不是 JSON 对象".to_string()))?;
                // 提取 id 和 name（允许缺失，可根据需求调整是否报错）
                let description = obj.get("description").cloned(); // 克隆 Value 保留原始类型（数字/字符串）
                let icon_url = obj.get("icon_url").cloned();
                let id = obj.get("id").cloned();
                let is_published = obj.get("is_published").cloned();
                let name = obj.get("name").cloned();
                let owner_user_id = obj.get("owner_user_id").cloned();
                let published_at = obj.get("published_at").cloned();
                let updated_at = obj.get("updated_at").cloned();
                // 构建只包含 id 和 name 的新对象
                let mut new_obj = Map::new();
                new_obj.insert("aiAgentDescription".to_string(), description.unwrap());
                new_obj.insert("aiAgentIconUrl".to_string(), icon_url.unwrap());
                new_obj.insert("aiAgentBotId".to_string(), id.unwrap());
                // new_obj.insert("is_published".to_string(), is_published.unwrap());
                new_obj.insert("aiAgentName".to_string(), name.unwrap());
                // new_obj.insert("owner_user_id".to_string(), owner_user_id.unwrap());
                // new_obj.insert("published_at".to_string(), published_at.unwrap());
                // new_obj.insert("updated_at".to_string(), updated_at.unwrap());

                // 跳过既没有 id 也没有 name 的对象（可选）
                if !new_obj.is_empty() {
                    new_array.push(Value::Object(new_obj));
                }
            }

            return Ok(Value::Array(new_array));
        }
        Err(e) => {
            // 失败：处理错误（如打印日志）
            eprintln!("请求失败: {}", e);
            let errmsg = AppError::ValidationFailed(format!("基本错误,{}", e.to_string()));
            return Err(errmsg);
        }
    }
}

pub async fn get_check_ai_agent_sta(db: &MySqlPool, ai_agent_id: u64) -> Result<(), AppError> {
    println!("结构体信息:1 ");
    let ai_agent_do = select_do_ai_agent_by_id(db, ai_agent_id).await?;
    println!("结构体信息: {:?}", ai_agent_do);
    let way: &str = "get";
    let url: &str = "https://api.coze.cn/v1/bots?";
    let token: String = "Bearer ".to_string()
        + ai_agent_do
            .ai_agent_plat_access_token
            .as_ref()
            .expect("access_token 为空");

    let headers = coze_constants::with_auth_headers(&token);
    // 调用 request_json 并手动处理 Result
    let result = http_utils::request_json(way, url, headers, None).await;
    match result {
        Ok(data) => {
            // 成功：处理返回的 JSON 数据（data1 是 Value 类型）
            println!("请求成功: {:?}", data);
            // 可以在这里对 data1 进行后续处理（如提取字段、存储等）
        }
        Err(e) => {
            // 失败：处理错误（如打印日志）
            eprintln!("请求失败: {}", e);
            let errmsg = AppError::ValidationFailed(format!("基本错误,{}", e.to_string()));
            return Err(errmsg);
            // 也可以选择忽略错误（不推荐，可能隐藏问题）
            // return; // 提前返回
        }
    }

    return Ok(());
}
